﻿-- Function: pl_function.generar_cuenta(character varying, character varying, character varying, character varying)

-- DROP FUNCTION pl_function.generar_cuenta(character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION pl_function.generar_cuenta(p_mhresum character varying, p_mperson character varying, p_vhostnm character varying, p_vusernm character varying)
  RETURNS integer AS
$BODY$
declare
	p_idsigma integer;
	p_corigen character varying(10);
	p_mhresum_old character varying(10);
begin
	select coalesce(max(idsigma)::numeric(18, 0), 0) into p_idsigma from tesoreria.mestcta; 

	select idsigma into p_mhresum_old from registro.mhresum where mperson=p_mperson and nestado=0 order by idsigma desc limit 1;
	select ctipdat into p_corigen from registro.mhresum where idsigma=p_mhresum;
	
	insert into tesoreria.mestcta(
		  idsigma, cidpers, cidpred
		, ctiping, ctiprec, cperiod
		, ncantid, imp_insol, fact_reaj, imp_reaj
		, fact_mora, imp_mora, costo_emis
		, dfecven, dfecpag, vobserv
		, nestado, vusernm, vhostnm
		, ddatetm, cperanio, mhresum, mpredio, corigen
	) select lpad(((row_number() over(order by trim(coalesce(a.cidpred, '')))) + p_idsigma)::varchar, 10, '0') idsigma
		, $2 cidpers
		, trim(coalesce(a.cidpred, ''))
		, a.ctiping
		, a.ctiprec
		, a.cmescuo cperiod
		, 1 ncantid
		, a.ninsolu imp_insol
		, 0 fact_reaj
		, 0 imp_reaj
		, 0 fact_mora
		, 0 imp_mora
		, 0 costo_emis
		, a.dfecven
		, null dfecpag
		, '' vobserv
		, 0 nestado
		, p_vusernm vusernm
		, p_vhostnm vhostnm
		, now() ddatetm
		, a.cperanio
		, $1
		, trim(coalesce(a.mpredio, ''))
		, p_corigen corigen
	from (
		select a.cidpred,
			a.ctiping,
			a.ctiprec,
			(a.ninsolu - coalesce(b.ninsolu, 0.00)) ninsolu,
			b.ncancel,
			a.cperanio,
			a.mpredio,
			a.dfecadq,
			a.dfecdes,
			a.dfecven,
			a.cmescuo
		from pl_select.listar_estcta(p_mhresum) a
		left join pl_select.listar_estcta(p_mhresum_old, p_mperson) b on
			a.cperanio=b.cperanio and
			a.ctiprec=b.ctiprec and
			a.cmescuo=b.cmescuo and
			('0' || coalesce(a.cidpred, '0'))::int=('0' || coalesce(b.cidpred, '0'))::int
		where (a.ninsolu - coalesce(b.ninsolu, 0.00)) <> 0 and not(
			(a.ninsolu - coalesce(b.ninsolu, 0.00))  < 0 and coalesce(b.ncancel, 0) > 0
		)
	) a;

	update registro.mhresum set nrecalc=1 where idsigma=p_mhresum;
	return 1;
exception
when sqlstate '57014' then
	raise notice 'Error';
	return 0;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pl_function.generar_cuenta(character varying, character varying, character varying, character varying)
  OWNER TO postgres;
